/*
* created by kaweepapkongkittisan
*
*/


package com.tuinui.trade;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class StockPriceUpdaterExcelPOIImpl extends BasePriceUpdateUtility implements IStockPriceUpdater{
	protected String source;
	
	@Override
	public void updatePrice(List<StockTradeDailyEntry> ent) throws Exception {
		FileInputStream in = new FileInputStream(new File(getSource()));
		
		//Get the workbook instance for XLS file 
		XSSFWorkbook workbook = new XSSFWorkbook(in);
		
		//wb = WorkbookFactory.create(new FileInputStream(new File(getSource())));
		
		//HSSFWorkbook workbook = new HSSFWorkbook(in);
		//HSSFSheet sheet = workbook.getSheet("Price Today");
		XSSFSheet sheet = workbook.getSheet("Price Today");
		
		
		//Iterator<Row> rowIterator = sheet.iterator();
		clearSheet(sheet, -1);
		
		in.close();
		writeToSheet(ent, sheet);
		FileOutputStream out = new FileOutputStream(new File(getSource()));
		workbook.write(out);
		out.close();
	
	}

	@Override
	public String getSource() {
		// TODO Auto-generated method stub
		return source;
	}

	@Override
	public void setSource(String source) {
		this.source = source;
		
	}
	
	public void clearSheet(XSSFSheet sheet, int startRow)throws Exception{
		
		for (int i=sheet.getLastRowNum(); i>0; i--){
			if (i>startRow){
				Row r = sheet.getRow(i);
				sheet.removeRow(r);
			}
		}
	}
	
	public  void writeToSheet(List<StockTradeDailyEntry> entries, XSSFSheet sheet)throws Exception{
		Iterator<Row> rowIterator = sheet.iterator();
		int rowNum = 0;
		Iterator<StockTradeDailyEntry> priceIterator = entries.iterator();
		while(priceIterator.hasNext()){
			rowNum++;
			StockTradeDailyEntry entry = priceIterator.next();
			Row row = sheet.createRow(rowNum);
			int colNum = 0;

			pushCell(row, colNum++, entry.getDate());
			
			pushCell(row, colNum++, entry.getSymbol() );
			pushCell(row, colNum++, entry.getSymbol() );
			pushCell(row, colNum++, entry.getFlag() );
			pushCell(row, colNum++, entry.getOpen() );
			pushCell(row, colNum++, entry.getHigh() );
			pushCell(row, colNum++, entry.getLow() );
			pushCell(row, colNum++, entry.getLast() );
			pushCell(row, colNum++, entry.getChange() );
			pushCell(row, colNum++, entry.getPercentChange() );
			pushCell(row, colNum++, entry.getBid() );
			pushCell(row, colNum++, entry.getOffer() );
			pushCell(row, colNum++, entry.getVolumeTrade() );
			pushCell(row, colNum++, entry.getValueTrade() );

		
		}
	}
	
	public void pushCell(Row row, int col, BigDecimal b)throws Exception{
		CellStyle style = row.getSheet().getWorkbook().createCellStyle();
		style.setDataFormat((short)2);
		
		row.createCell(col);
		if (b!=null){
			row.getCell(col).setCellValue(b.doubleValue());
			row.getCell(col).setCellStyle(style);
		}

	}
	
	public void pushCell(Row row, int col, String s)throws Exception{
		
		row.createCell(col);
		if (s!=null)
			row.getCell(col).setCellValue(s);

	}
	
	public void pushCell(Row row, int col, Date d)throws Exception{
		
		row.createCell(col);
		if (d!=null){
			CellStyle style = row.getSheet().getWorkbook().createCellStyle();
			DataFormat df = row.getSheet().getWorkbook().createDataFormat();
			style.setDataFormat(df.getFormat("d-mmm-yy"));
			row.getCell(col).setCellValue(d);
			row.getCell(col).setCellStyle(style);
		}
	}
}
